package com.dao;

import com.model.Goods;
import com.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDAO {
    Connection conn = null;
    PreparedStatement pstmt= null;
    ResultSet rs = null;
    int  twe=0;
    public void addGoods(Goods goods) {
        String sql = "INSERT INTO goods (id,name, price, image_path,mark,quantity) VALUES (?,?, ?, ?,?,?)";

        try {
            conn = JDBCUtil.getCon();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, goods.getId());
            pstmt.setString(2, goods.getName());
            pstmt.setDouble(3, goods.getPrice());
            pstmt.setString(4, goods.getImagePath());
            pstmt.setString(5, goods.getMark());
            pstmt.setDouble(6, goods.getQuantity());
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public List<Goods> getAllGoods() {
        List<Goods> goodsList =new ArrayList<>();
        try {
            conn = JDBCUtil.getCon();
            String sql = "select  * from goods";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Goods goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getDouble("price"));
                goods.setImagePath(rs.getString("image_path"));
                goods.setMark(rs.getString("mark"));
                goods.setQuantity(rs.getDouble("quantity"));

                goodsList.add(goods);
            }
            for (Goods goods1 : goodsList) {
                System.out.println(goods1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtil.colse(rs, pstmt, conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return goodsList;
    }
    public List<Goods> getAllGoods01() {
        List<Goods> goodsList =new ArrayList<>();
        try {
            conn = JDBCUtil.getCon();
            String sql = "select id, name,price,quantity from goods";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Goods goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getDouble("price"));
                goods.setQuantity(rs.getDouble("quantity"));

                goodsList.add(goods);
            }
            for (Goods goods1 : goodsList) {
                System.out.println(goods1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtil.colse(rs, pstmt, conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return goodsList;
    }

    public boolean deleteGoods(int id) {
        String sql = "DELETE FROM goods WHERE id = ?";
        try {
            conn = JDBCUtil.getCon();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int rowsAffected = pstmt.executeUpdate();
            return rowsAffected > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            try {
                JDBCUtil.colse(pstmt,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }
    public Goods getGoodsById(int id) {
        Goods goods = null;
        conn = JDBCUtil.getCon();
        String sql = "SELECT * FROM goods WHERE id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setObject(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getDouble("price"));
                goods.setImagePath(rs.getString("image_path"));
                goods.setMark(rs.getString("mark"));
                goods.setQuantity(rs.getDouble("quantity"));
            }
            System.out.println(goods);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JDBCUtil.colse(rs, pstmt, conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return goods;
    }
    public int updateGoods(Goods goods) {
        System.out.println(goods);
        conn = JDBCUtil.getCon();
        String sql = "update goods set name = ?, price = ?,mark=?,quantity=? WHERE id = ?";

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, goods.getName());
            pstmt.setDouble(2, goods.getPrice());
            pstmt.setString(3, goods.getMark());
            pstmt.setDouble(4, goods.getQuantity());
            pstmt.setInt(5, goods.getId());
            twe = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtil.colse(pstmt,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return twe;
    }
    public int updateGoods01(Goods goods) {
        System.out.println(goods);
        conn = JDBCUtil.getCon();
        String sql = "update goods set quantity=? WHERE id = ?";

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, goods.getQuantity());
            pstmt.setInt(2, goods.getId());
            twe = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtil.colse(pstmt,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return twe;
    }

    public List<Goods> mohuchaxun(String n){
        List<Goods> goodsList =new ArrayList<>();
        try {
            conn = JDBCUtil.getCon();
            String sql = "select  * from goods where name like concat('%',?,'%')";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,n);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Goods goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setPrice(rs.getDouble("price"));
                goods.setImagePath(rs.getString("image_path"));
                goods.setMark(rs.getString("mark"));
                goods.setQuantity(rs.getDouble("quantity"));
                goodsList.add(goods);
            }
            for (Goods goods1 : goodsList) {
                System.out.println(goods1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                JDBCUtil.colse(rs, pstmt, conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return goodsList;
    }

    public  void increaseQuantity(int id) {
        conn = JDBCUtil.getCon();
        String sql = "UPDATE goods SET price = price + 1 WHERE id = ?";

        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JDBCUtil.colse(pstmt,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public void decreaseQuantity(int id) {
        conn = JDBCUtil.getCon();
        String sql = "UPDATE goods SET price = price - 1 WHERE id = ?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                JDBCUtil.colse(pstmt,conn);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}


